-- --------------------------------------------------
-- Entity Designer DDL Script for Oracle database
-- --------------------------------------------------
-- Date Created: 2011/10/7 13:31:19
-- Generated from EDMX file: E:\Win7\Users\Yvonne\Documents\Visual Studio 2010\Projects\Framework\Infrastructure.Persistance.Oracle\Context\SampleModel.edmx
-- --------------------------------------------------

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

-- ALTER TABLE "sys"."UserGroup" DROP CONSTRAINT "FK_UserGroup_User" CASCADE;

-- ALTER TABLE "sys"."UserGroup" DROP CONSTRAINT "FK_UserGroup_Group" CASCADE;

-- ALTER TABLE "sys"."Items_MBlog" DROP CONSTRAINT "FK_UserMBlog" CASCADE;

-- ALTER TABLE "sys"."Comments" DROP CONSTRAINT "FK_UserComment" CASCADE;

-- ALTER TABLE "sys"."Comments" DROP CONSTRAINT "FK_ItemComment" CASCADE;

-- ALTER TABLE "sys"."Items_User" DROP CONSTRAINT "FK_User_inherits_Item" CASCADE;

-- ALTER TABLE "sys"."Items_MBlog" DROP CONSTRAINT "FK_MBlog_inherits_Item" CASCADE;

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

-- DROP TABLE "sys"."Groups";

-- DROP TABLE "sys"."Comments";

-- DROP TABLE "sys"."Items";

-- DROP TABLE "sys"."Items_User";

-- DROP TABLE "sys"."Items_MBlog";

-- DROP TABLE "sys"."UserGroup";

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Groups'
CREATE TABLE "SYSTEM"."Groups" (
   "ID" RAW(16) NOT NULL,
   "Name" NCLOB NOT NULL
);

-- Creating table 'Comments'
CREATE TABLE "SYSTEM"."Comments" (
   "ID" RAW(16) NOT NULL,
   "Body" NCLOB NULL,
   "Title" NCLOB NULL,
   "CreateTime" DATE NOT NULL,
   "AuthorID" RAW(16) NOT NULL,
   "Type" NCLOB NOT NULL,
   "ItemID" RAW(16) NOT NULL,
   "Publisher_ID" RAW(16) NOT NULL
);

-- Creating table 'Items'
CREATE TABLE "SYSTEM"."Items" (
   "ID" RAW(16) NOT NULL
);

-- Creating table 'Items_User'
CREATE TABLE "SYSTEM"."Items_User" (
   "Name" NCLOB NOT NULL,
   "Address" NCLOB NULL,
   "ID" RAW(16) NOT NULL
);

-- Creating table 'Items_MBlog'
CREATE TABLE "SYSTEM"."Items_MBlog" (
   "Body" NCLOB NOT NULL,
   "CreateTime" DATE NOT NULL,
   "Status" NUMBER(10,0) NOT NULL,
   "UserID" RAW(16) NOT NULL,
   "ID" RAW(16) NOT NULL,
   "Author_ID" RAW(16) NOT NULL
);

-- Creating table 'UserGroup'
CREATE TABLE "SYSTEM"."UserGroup" (
   "Members_ID" RAW(16) NOT NULL,
   "Groups_ID" RAW(16) NOT NULL
);


-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on "ID"in table 'Groups'
ALTER TABLE "SYSTEM"."Groups"
ADD CONSTRAINT "PK_Groups"
   PRIMARY KEY ("ID" )
   ENABLE
   VALIDATE;


-- Creating primary key on "ID"in table 'Comments'
ALTER TABLE "SYSTEM"."Comments"
ADD CONSTRAINT "PK_Comments"
   PRIMARY KEY ("ID" )
   ENABLE
   VALIDATE;


-- Creating primary key on "ID"in table 'Items'
ALTER TABLE "SYSTEM"."Items"
ADD CONSTRAINT "PK_Items"
   PRIMARY KEY ("ID" )
   ENABLE
   VALIDATE;


-- Creating primary key on "ID"in table 'Items_User'
ALTER TABLE "SYSTEM"."Items_User"
ADD CONSTRAINT "PK_Items_User"
   PRIMARY KEY ("ID" )
   ENABLE
   VALIDATE;


-- Creating primary key on "ID"in table 'Items_MBlog'
ALTER TABLE "SYSTEM"."Items_MBlog"
ADD CONSTRAINT "PK_Items_MBlog"
   PRIMARY KEY ("ID" )
   ENABLE
   VALIDATE;


-- Creating primary key on "Members_ID", "Groups_ID"in table 'UserGroup'
ALTER TABLE "SYSTEM"."UserGroup"
ADD CONSTRAINT "PK_UserGroup"
   PRIMARY KEY ("Members_ID", "Groups_ID" )
   ENABLE
   VALIDATE;


-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on "Members_ID" in table 'UserGroup'
ALTER TABLE "SYSTEM"."UserGroup"
ADD CONSTRAINT "FK_UserGroup_User"
   FOREIGN KEY ("Members_ID")
   REFERENCES "SYSTEM"."Items_User"
       ("ID")
   ENABLE
   VALIDATE;

-- Creating index for FOREIGN KEY 'FK_UserGroup_User'
CREATE INDEX "IX_FK_UserGroup_User"
ON "SYSTEM"."UserGroup"
   ("Members_ID");

-- Creating foreign key on "Groups_ID" in table 'UserGroup'
ALTER TABLE "SYSTEM"."UserGroup"
ADD CONSTRAINT "FK_UserGroup_Group"
   FOREIGN KEY ("Groups_ID")
   REFERENCES "SYSTEM"."Groups"
       ("ID")
   ENABLE
   VALIDATE;

-- Creating index for FOREIGN KEY 'FK_UserGroup_Group'
CREATE INDEX "IX_FK_UserGroup_Group"
ON "SYSTEM"."UserGroup"
   ("Groups_ID");

-- Creating foreign key on "Author_ID" in table 'Items_MBlog'
ALTER TABLE "SYSTEM"."Items_MBlog"
ADD CONSTRAINT "FK_UserMBlog"
   FOREIGN KEY ("Author_ID")
   REFERENCES "SYSTEM"."Items_User"
       ("ID")
   ENABLE
   VALIDATE;

-- Creating index for FOREIGN KEY 'FK_UserMBlog'
CREATE INDEX "IX_FK_UserMBlog"
ON "SYSTEM"."Items_MBlog"
   ("Author_ID");

-- Creating foreign key on "Publisher_ID" in table 'Comments'
ALTER TABLE "SYSTEM"."Comments"
ADD CONSTRAINT "FK_UserComment"
   FOREIGN KEY ("Publisher_ID")
   REFERENCES "SYSTEM"."Items_User"
       ("ID")
   ENABLE
   VALIDATE;

-- Creating index for FOREIGN KEY 'FK_UserComment'
CREATE INDEX "IX_FK_UserComment"
ON "SYSTEM"."Comments"
   ("Publisher_ID");

-- Creating foreign key on "ItemID" in table 'Comments'
ALTER TABLE "SYSTEM"."Comments"
ADD CONSTRAINT "FK_ItemComment"
   FOREIGN KEY ("ItemID")
   REFERENCES "SYSTEM"."Items"
       ("ID")
   ENABLE
   VALIDATE;

-- Creating index for FOREIGN KEY 'FK_ItemComment'
CREATE INDEX "IX_FK_ItemComment"
ON "SYSTEM"."Comments"
   ("ItemID");

-- Creating foreign key on "ID" in table 'Items_User'
ALTER TABLE "SYSTEM"."Items_User"
ADD CONSTRAINT "FK_User_inherits_Item"
   FOREIGN KEY ("ID")
   REFERENCES "SYSTEM"."Items"
       ("ID")
   ENABLE
   VALIDATE;

-- Creating foreign key on "ID" in table 'Items_MBlog'
ALTER TABLE "SYSTEM"."Items_MBlog"
ADD CONSTRAINT "FK_MBlog_inherits_Item"
   FOREIGN KEY ("ID")
   REFERENCES "SYSTEM"."Items"
       ("ID")
   ENABLE
   VALIDATE;

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------
